import re
import pymysql
from pyhanlp import *


# 识别个人信息方法
def identify_info(value):
    # 1.识别手机号
    def identify_phone(value):
        try:
            int(value)
            phone_pattern = '^[1](([3][0-9])|([4][5-9])|([5][0-3,5-9])|([6][5,6])|([7][0-8])|([8][0-9])|([9][1,8,9]))[0-9]{8}$'
            if re.match(phone_pattern, value):
                return '手机号', value
            else:
                return '非法输入', value
        except ValueError:
            return '非法输入', value

    # 2.识别身份证号
    def identify_pii(value):
        str_to_int = {'0': 0, '1': 1, '2': 2, '3': 3, '4': 4, '5': 5,
                      '6': 6, '7': 7, '8': 8, '9': 9, 'X': 10}
        check_dict = {0: '1', 1: '0', 2: 'X', 3: '9', 4: '8', 5: '7',
                      6: '6', 7: '5', 8: '4', 9: '3', 10: '2'}
        if len(value) != 18:
            # raise TypeError(u'请输入标准的第二代身份证号码')
            return '非法输入', value
        try:
            check_num = 0
            for index, num in enumerate(value):
                if index == 17:
                    verify_code = check_dict.get(check_num % 11)
                    if num == verify_code:
                        # print(u"身份证号: %s, 校验通过," % value)
                        return '身份证号', value
                    else:
                        # print(u"身份证号: %s, 校验不通过, 正确尾号应该为：%s," % (value, verify_code))
                        return '非法输入', value
                check_num += str_to_int.get(num) * (2 ** (17 - index) % 11)
        except TypeError:
                return '非法输入', value

    # 3.识别银行卡
    def identify_bank_card(value):
        total = 0
        card_num_length = len(value)
        if card_num_length <= 11:
            return ('非法输入', value)
        for item in range(1, card_num_length + 1):
            try:
                t = int(value[card_num_length - item])
                if item % 2 == 0:
                    t *= 2
                    total += t if t < 10 else t % 10 + t // 10
                else:
                    total += t
            except ValueError:
                return ('非法输入', value)
        if total % 10 == 0:
            return ('银行卡号', value)
        else:
            return ('非法输入', value)

    # 4.识别地址信息
    """
    导入pyhanlp模块，输入值地址开头且地址统计>=3即判断为地址信息
    """
    def identify_address(value):
        if not value:
            return '其他', '其他'
        CRFnewSegment = HanLP.newSegment("crf")
        address_list = CRFnewSegment.seg(value)
        dict = {}
        for i in address_list:
            dict[str(i.word)] = [str(i.nature)]

        ADDR_CODE = r'(ns|nsf)'  # 地址：ns|nsf  || 姓名：nr
        addr_count = len(list(filter(lambda x: x == ['ns'] or x == ['nsf'], dict.values())))
        for addr, code in dict.items():
            code = str(code)
            if re.search(ADDR_CODE, code) and addr_count >= 3:
                return '地址', value
            else:
                return '非法输入', value

    # 数据库结果集识别结果输出
    if identify_phone(value)[0] == '手机号':
        return identify_phone(value)
    elif identify_pii(value)[0] == '身份证号':
        return identify_pii(value)
    elif identify_bank_card(value)[0] == '银行卡':
        return identify_bank_card(value)
    elif identify_address(value)[0] == '地址':
        return identify_address(value)
    else:
        return '其他',value

class DB:
    def __init__(self,ip,username,password,port,database,charset):
        self.ip = ip
        self.port = port
        self.database = database
        self.username = username
        self.password = password
        self.charset = charset
        self.db = pymysql.connect(host=self.ip,user=self.username,password=self.password,port=self.port,database=self.database,charset=self.charset)
        self.cursor = self.db.cursor()

    # 通过schemata获取所有数据库名称
    def get_database(self):
        self.cursor.execute("SELECT schema_name from information_schema.schemata")
        database_list = self.cursor.fetchall()
        result = []
        for line in database_list:
            if line[0] not in ['information_schema','mysql','performance_schema','hadoop_hive']:
                result.append(line[0])
        return result

    # 获取数据库对应表名
    def get_table(self,database):
        self.cursor.execute("select table_name from information_schema.tables where table_schema='%s'" % database)
        table_list = self.cursor.fetchall()
        result = []
        for line in table_list:
            result.append(line[0])
        return result

    # 获取字段名
    def get_column(self,database,table):
        self.cursor.execute("select column_name from information_schema.columns where table_schema='%s' and table_name='%s'" %(database,table))
        column_list = self.cursor.fetchall()
        result = []
        for line in column_list:
            result.append(line[0])
        return result

    # 获取字段内容
    def get_content(self,database,table,column):
        # 增加采样数据，随机抽取5条数据进行验证，暂不考虑SQL查询语句性能
        self.cursor.execute("select %s from %s.%s order by rand() limit 3" %(column,database,table))
        contents = self.cursor.fetchall()
        if contents:
            return contents
        else:
            return tuple()

if __name__ == '__main__':
    db = DB('127.0.0.1','root','root',3307,'dsm','utf8')
    # target_db = pymysql.connect(host='127.0.0.1',user='root',password='root',port=3308,database='encrypt_db')
    databases = db.get_database()
    for database in databases:
        tables = db.get_table(database)
        for table in tables:
            columns = db.get_column(database,table)
            for column in columns:
                querydata = db.get_content(database,table,column)
                for data in querydata:
                    data_str = str(data[0])
                    result = [database,table,column,identify_info(data_str)[1],identify_info(data_str)[0]]
                    print(result)
                    # # save to mysql
                    # database_name = result[0]
                    # table_name = result[1]
                    # column_name = result[2]
                    # simpling01 = result[3]
                    # classify = result[4]
                    # args = (database_name,table_name,column_name,simpling01,classify)
                    # print(args)
                    # sql = """insert into sdsm_metadata(database_name,table_name,column_name,simpling01,classify) values (%s,%s,%s,%s,%s)"""
                    # cursor = target_db.cursor()
                    # cursor.execute(sql,args)
                    # target_db.commit()
                    # save to local file
                    result_str = str(result) + "\r\n"
                    print(result_str)
                    with open('db_classify_result.txt','a+',encoding='utf-8') as f:
                        f.write(result_str)

    # target_db.close()